/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package excelprocess;

import java.io.File;
import java.net.URLEncoder;
import java.util.Properties;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 
 * @author EricZhou
 */
public class ExcelProcess {

	private String filepath;

	/**
	 * @param args
	 *            the command line arguments
	 */

	public void setPath(String path) {
		this.filepath = path;
	}

	public String getPath() {
		return this.filepath;
	}

	public Cell ReadCell(int column, int row) {
		// String path = "D:\\Cpic\\IDS_Model\\P10_IDS Providing Model.xls";
		Cell cell = null;
		// TODO code application logic here
		try {
			Workbook book = Workbook.getWorkbook(new File(filepath));

			Sheet sheet = book.getSheet(0);

			// Cell cell1 = sheet.getCell(0, 0);
			cell = sheet.getCell(column, row);
			// cells = sheet.getRow(row);
			int rowNum = sheet.getRows();
			// int columnNum = sheet.getColumns();
			/*
			 * String VersionDate = cell.getContents();
			 * 
			 * //String result =VersionDate.replaceFirst("发布", "");
			 * System.out.println("Rows are " + rowNum);
			 * System.out.println(VersionDate);
			 */
			book.close();

		} catch (Exception e) {
			System.out.println(e);
		}

		return cell;
	}

	public Cell[] ReadRow(int row) {
		// String path = "D:\\Cpic\\IDS_Model\\P10_IDS Providing Model.xls";
		Cell[] cells = null;
		// TODO code application logic here
		try {
			Workbook book = Workbook.getWorkbook(new File(filepath));

			Sheet sheet = book.getSheet(0);

			cells = sheet.getRow(row);

			for (int i = 0; i < cells.length; i++) {
				System.out.print(cells[i].getContents() + " ");
			}
			book.close();

		} catch (Exception e) {
			System.out.println(e);
		}
		return cells;
	}

	public String FindCell(int column1, int column2) {
		// String path = "D:\\Cpic\\IDS_Model\\P10_IDS Providing Model.xls";
		Cell cell1 = null; //The MODNO
		Cell cell2 = null;//The CONTENT
		Cell cell3=null; //The MODNO cursor
		String result = "";
		
		// TODO code application logic here
		try {
			Workbook book = Workbook.getWorkbook(new File(filepath));
			// list
			MyArrayList list = new MyArrayList();
			list.add("MODNO");

			Sheet sheet = book.getSheet(0);
			System.out.println("Got sheet.");
			System.out.println("RowHeight=" + sheet.getRows());

			for (int i = 0; i < sheet.getRows(); i++) {
				boolean isFirst = true;
				// System.out.println("i="+i);
				// Get the cell
				cell1 = sheet.getCell(column1, i);

				// Check if it has been appeared
				if (!list.contains(cell1.getContents())) {
					list.add(cell1.getContents());
				} else {
					continue;
				}
				for (int j = i; j < sheet.getRows(); j++) {
					cell3=sheet.getCell(column1,j);
					if (cell1.getContents().equals(cell3.getContents())) {
						if (isFirst) {
							result += cell1.getContents() + "#####";
							isFirst = false;
						}
						System.out.println("j=" + j);
						cell2 = sheet.getCell(column2, j);
						result += cell2.getContents();

					}
				}
				result+="%%%%%\n";
			}
			book.close();

		} catch (Exception e) {
			System.out.println(e);
		}

		return result;
	}

	public String FindCell(int column1, int column2, String content) {
		// String path = "D:\\Cpic\\IDS_Model\\P10_IDS Providing Model.xls";
		Cell cell1 = null;
		Cell cell2 = null;
		String result = "";
		boolean isFirst = true;
		// TODO code application logic here
		try {
			Workbook book = Workbook.getWorkbook(new File(filepath));

			Sheet sheet = book.getSheet(0);
			System.out.println("Got sheet.");
			System.out.println("RowHeight=" + sheet.getRows());
			for (int i = 0; i < sheet.getRows(); i++) {
				// System.out.println("i="+i);
				cell1 = sheet.getCell(column1, i);
				if (cell1.getContents().equals(content)) {
					if (isFirst) {
						result += cell1.getContents() + "#####";
						isFirst = false;
					}
					System.out.println("i=" + i);
					cell2 = sheet.getCell(column2, i);
					result += cell2.getContents();

				}
			}
			book.close();

		} catch (Exception e) {
			System.out.println(e);
		}

		return result;
	}

	public void modifyExcel() {
		try {
			Workbook wb = Workbook.getWorkbook(new File(filepath));
			WritableWorkbook wwb = Workbook.createWorkbook(new File(filepath),
					wb);

			WritableSheet sheets[] = wwb.getSheets();
			for (int s = 0; s < sheets.length; s++) {
				int rows = sheets[s].getRows();
				for (int i = 1; i < rows; i++) {
					Cell cel = sheets[s].getCell(2, i);
					String kw = cel.getContents();
					if (kw != null) {
						String kwe = URLEncoder.encode(kw.trim(), "UTF-8");
						sheets[s].addCell(new Label(3, i, kwe));
					}
				}
			}
			wwb.write();
			wwb.close();
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
